package repositories

import (
	"context"

	"gorm.io/gorm"

	"github.com/samaa/photosalesplus/backend/internal/models"
)

type ProductSalesStat struct {
	ProductID    uint
	OrdersCount  int64
	UnitsSold    int64
	TotalRevenue float64
}

type ProductSalesDetail struct {
	ProductID    uint    `json:"product_id"`
	Title        string  `json:"title"`
	CoverImage   string  `json:"cover_image"`
	OrdersCount  int64   `json:"orders_count"`
	UnitsSold    int64   `json:"units_sold"`
	TotalRevenue float64 `json:"total_revenue"`
}

type CustomerLeaderboardEntry struct {
	UserID      uint    `json:"user_id"`
	Username    string  `json:"username"`
	DisplayName string  `json:"display_name"`
	TotalSpent  float64 `json:"total_spent"`
	OrdersCount int64   `json:"orders_count"`
}

type OrderSummary struct {
	TotalOrders     int64   `json:"total_orders"`
	CompletedOrders int64   `json:"completed_orders"`
	PendingOrders   int64   `json:"pending_orders"`
	CanceledOrders  int64   `json:"canceled_orders"`
	TotalRevenue    float64 `json:"total_revenue"`
}

type OrderRepository interface {
	Create(ctx context.Context, order *models.Order) error
	ListByCustomer(ctx context.Context, customer string) ([]models.Order, error)
	ProductStatsByIDs(ctx context.Context, productIDs []uint) (map[uint]ProductSalesStat, error)
	Summary(ctx context.Context) (OrderSummary, error)
	Recent(ctx context.Context, limit int) ([]models.Order, error)
	TopProductStats(ctx context.Context, limit int) ([]ProductSalesDetail, error)
	ExistsCustomerOrderForProduct(ctx context.Context, customer string, productID uint) (bool, error)
	PurchasedProductIDsByCustomer(ctx context.Context, customer string) ([]uint, error)
	CustomerLeaderboard(ctx context.Context, limit, offset int) ([]CustomerLeaderboardEntry, error)
	UpdateStatus(ctx context.Context, orderID uint, status string) error
}

type orderRepository struct {
	db *gorm.DB
}

func NewOrderRepository(db *gorm.DB) OrderRepository {
	return &orderRepository{db: db}
}

func (r *orderRepository) Create(ctx context.Context, order *models.Order) error {
	return r.db.WithContext(ctx).Create(order).Error
}

func (r *orderRepository) ListByCustomer(ctx context.Context, customer string) ([]models.Order, error) {
	var orders []models.Order
	tx := r.db.WithContext(ctx).Preload("Items")
	if customer != "" {
		tx = tx.Where("customer = ?", customer)
	}
	if err := tx.Order("created_at DESC").Find(&orders).Error; err != nil {
		return nil, err
	}
	return orders, nil
}

func (r *orderRepository) ProductStatsByIDs(ctx context.Context, productIDs []uint) (map[uint]ProductSalesStat, error) {
	stats := make(map[uint]ProductSalesStat)
	if len(productIDs) == 0 {
		return stats, nil
	}

	type row struct {
		ProductID    uint
		OrdersCount  int64
		UnitsSold    int64
		TotalRevenue float64
	}

	var rows []row
	if err := r.db.WithContext(ctx).
		Table("order_items AS oi").
		Select("oi.product_id AS product_id, COUNT(DISTINCT oi.order_id) AS orders_count, COUNT(oi.id) AS units_sold, COALESCE(SUM(oi.price), 0) AS total_revenue").
		Joins("JOIN orders o ON o.id = oi.order_id").
		Where("oi.product_id IN ?", productIDs).
		Where("o.status = ?", "completed").
		Group("oi.product_id").
		Scan(&rows).Error; err != nil {
		return nil, err
	}

	for _, item := range rows {
		stats[item.ProductID] = ProductSalesStat{
			ProductID:    item.ProductID,
			OrdersCount:  item.OrdersCount,
			UnitsSold:    item.UnitsSold,
			TotalRevenue: item.TotalRevenue,
		}
	}

	return stats, nil
}

func (r *orderRepository) Summary(ctx context.Context) (OrderSummary, error) {
	type row struct {
		Status  string
		Count   int64
		Revenue float64
	}

	var rows []row
 if err := r.db.WithContext(ctx).
    Model(&models.Order{}).
    Where("status = ?", "completed").
    Select("status, COUNT(*) AS count, COALESCE(SUM(total_amount), 0) AS revenue").
    Group("status").
    Scan(&rows).Error; err != nil {
		return OrderSummary{}, err
	}

	var summary OrderSummary
	for _, item := range rows {
		switch item.Status {
    case "completed":
        summary.CompletedOrders += item.Count
        summary.TotalRevenue += item.Revenue
    }
    summary.TotalOrders += item.Count
}
return summary, nil
}

func (r *orderRepository) Recent(ctx context.Context, limit int) ([]models.Order, error) {
    if limit <= 0 {
        limit = 5
    }
    var orders []models.Order
    if err := r.db.WithContext(ctx).
        Preload("Items").
        Where("status = ?", "completed").
        Order("created_at DESC").
        Limit(limit).
        Find(&orders).Error; err != nil {
        return nil, err
	}
	return orders, nil
}

func (r *orderRepository) TopProductStats(ctx context.Context, limit int) ([]ProductSalesDetail, error) {
	if limit <= 0 {
		limit = 5
	}

	type row struct {
		ProductID    uint
		Title        string
		CoverImage   string
		OrdersCount  int64
		UnitsSold    int64
		TotalRevenue float64
	}

	var rows []row
	if err := r.db.WithContext(ctx).
		Table("order_items AS oi").
		Select("oi.product_id AS product_id, p.title, p.cover_image, COUNT(DISTINCT oi.order_id) AS orders_count, COUNT(oi.id) AS units_sold, COALESCE(SUM(oi.price), 0) AS total_revenue").
		Joins("JOIN orders o ON o.id = oi.order_id AND o.status = ?", "completed").
		Joins("JOIN products p ON p.id = oi.product_id").
		Group("oi.product_id, p.title, p.cover_image").
		Order("total_revenue DESC").
		Limit(limit).
		Scan(&rows).Error; err != nil {
		return nil, err
	}

	var result []ProductSalesDetail
	for _, item := range rows {
		result = append(result, ProductSalesDetail{
			ProductID:    item.ProductID,
			Title:        item.Title,
			CoverImage:   item.CoverImage,
			OrdersCount:  item.OrdersCount,
			UnitsSold:    item.UnitsSold,
			TotalRevenue: item.TotalRevenue,
		})
	}

	return result, nil
}

func (r *orderRepository) ExistsCustomerOrderForProduct(ctx context.Context, customer string, productID uint) (bool, error) {
	type row struct{ C int64 }
	var out row
	err := r.db.WithContext(ctx).
		Table("orders AS o").
		Select("COUNT(*) AS c").
		Joins("JOIN order_items oi ON oi.order_id = o.id").
		Where("o.customer = ?", customer).
		Where("oi.product_id = ?", productID).
		Where("o.status = ?", "completed").
		Limit(1).
		Scan(&out).Error
	return out.C > 0, err
}

func (r *orderRepository) PurchasedProductIDsByCustomer(ctx context.Context, customer string) ([]uint, error) {
	var ids []uint
	if err := r.db.WithContext(ctx).
		Table("orders AS o").
		Select("DISTINCT oi.product_id").
		Joins("JOIN order_items oi ON oi.order_id = o.id").
		Where("o.customer = ?", customer).
		Where("o.status = ?", "completed").
		Scan(&ids).Error; err != nil {
		return nil, err
	}
	return ids, nil
}

func (r *orderRepository) CustomerLeaderboard(ctx context.Context, limit, offset int) ([]CustomerLeaderboardEntry, error) {
	type row struct {
		UserID      uint
		Username    string
		DisplayName string
		TotalSpent  float64
		OrdersCount int64
	}

	query := r.db.WithContext(ctx).
		Table("users AS u").
		Select(`
            u.id AS user_id,
            u.username AS username,
            COALESCE(NULLIF(u.display_name, ''), u.username) AS display_name,
            COUNT(o.id) AS orders_count,
            COALESCE(SUM(o.total_amount), 0) AS total_spent`).
		Joins("JOIN orders o ON o.customer = u.username AND o.status = ?", "completed").
		Where("u.role = ?", "customer").
		Group("u.id, u.username, u.display_name").
		Having("SUM(o.total_amount) > 0").
		Order("total_spent DESC, orders_count DESC, user_id ASC")

	if limit > 0 {
		query = query.Limit(limit)
	}
	if offset > 0 {
		query = query.Offset(offset)
	}

	var rows []row
	if err := query.Scan(&rows).Error; err != nil {
		return nil, err
	}

	result := make([]CustomerLeaderboardEntry, 0, len(rows))
	for _, item := range rows {
		result = append(result, CustomerLeaderboardEntry{
			UserID:      item.UserID,
			Username:    item.Username,
			DisplayName: item.DisplayName,
			TotalSpent:  item.TotalSpent,
			OrdersCount: item.OrdersCount,
		})
	}

	return result, nil
}

func (r *orderRepository) UpdateStatus(ctx context.Context, orderID uint, status string) error {
	return r.db.WithContext(ctx).Model(&models.Order{}).Where("id = ?", orderID).Update("status", status).Error
}
